package be.groept.ace.enquete.model.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.SortedSet;
import java.util.TreeSet;

import be.groept.ace.enquete.model.beans.Question;
import be.groept.ace.enquete.model.beans.Survey;
import be.groept.ace.enquete.model.beans.User;
import be.groept.ace.enquete.model.enums.Language;
import be.groept.ace.enquete.model.exceptions.DAOException;
import be.groept.ace.enquete.utils.SQLUtils;

public class SurveyDAOJDBCImpl implements SurveyDAO {

	private Connection connection;
	private UserDAOJDBCImpl userDAO;
	private QuestionDAOJDBCImpl questionDAO;
	public SurveyDAOJDBCImpl(Connection connection, UserDAO userDAO, QuestionDAO questionDAO) throws IllegalArgumentException{
		this.connection = connection;
		if (userDAO instanceof UserDAOJDBCImpl) {
			this.userDAO = (UserDAOJDBCImpl) userDAO;
		} else {
			throw new IllegalArgumentException("UserDAO object has to be an instance of the UserDAOJDBCImpl class");
		}
		if(questionDAO instanceof QuestionDAOJDBCImpl) {
			this.questionDAO = (QuestionDAOJDBCImpl) questionDAO;
		} else {
			throw new IllegalArgumentException("QuestionDAO object has to be an instance of the QuestionDAOJDBCImpl class");
		}
	}

	@Override
	public void insertSurvey(Survey survey) throws DAOException {
		PreparedStatement ps = null;
		ResultSet rsSurvey = null;
		
		try {
			String sql = "INSERT INTO survey (groupID, creationdate, startdate, enddate, title, description, creatorID, language) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
			ps = connection.prepareStatement( sql, Statement.RETURN_GENERATED_KEYS);
			if (survey.getGroupID() != -1) {
				ps.setInt(1, survey.getGroupID());
			} else {
				ps.setNull(1, Types.INTEGER);
			}
			ps.setTimestamp(2, SQLUtils.getCurrentDate());
			ps.setTimestamp(3, SQLUtils.convertToSQLTimestamp(survey.getStartdate()));
			ps.setTimestamp(4, SQLUtils.convertToSQLTimestamp(survey.getEnddate()));
			ps.setString(5,  survey.getTitle());
			ps.setString(6, survey.getDescription());
			ps.setInt(7, survey.getCreator().getId());
			ps.setString(8, survey.getLanguage().equals(Language.EN) ? "EN" : "NL");
			ps.executeUpdate();
			rsSurvey = ps.getGeneratedKeys();
			rsSurvey.next();
			survey.setId(rsSurvey.getInt(1));
		} catch (SQLException sqle) {
			throw new DAOException(sqle);
		} finally {
			if (rsSurvey != null) {
				try {
					rsSurvey.close();
				} catch (SQLException sqle) {
					//log error
				}
			}
			if (ps != null) {
				try {
					ps.close();
				} catch (SQLException sqle) {
					//log error
				}
			}
		}
		
	}

	@Override
	public SortedSet<Survey> findAllSurveys() throws DAOException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		SortedSet<Survey> surveySet = new TreeSet<Survey>();
		
		try {
			String sql = "SELECT * FROM survey INNER JOIN user ON user.id = survey.creatorID";
			ps = connection.prepareStatement( sql );
			rs = ps.executeQuery();
			while (rs.next()) {
				Survey survey = map(rs);
				User user = userDAO.map(rs);
				survey.setCreator(user);
				surveySet.add(survey);
			}
			
		} catch (SQLException sqle) {
			throw new DAOException(sqle);
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException sqle) {
					//log error
				}
			}
			
			if (ps != null) {
				try {
					ps.close();
				} catch (SQLException sqle) {
					//log error
				}
			}
		}
		return surveySet;
	}

	@Override
	public Survey findSurveyWithQuestionsAndCreator(int id) throws DAOException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		Survey survey = null;
		try {
			String sql = "SELECT * FROM survey INNER JOIN question ON question.surveyID = survey.id INNER JOIN user ON user.id = survey.creatorID WHERE survey.id = ?";
			ps = connection.prepareStatement( sql );
			ps.setInt(1, id);
			rs = ps.executeQuery();
			boolean first = true;
			while (rs.next()) {
				if(first) {
					survey = map(rs);
					User user = userDAO.map(rs);
					survey.setCreator(user);
					first = false;
				}
				Question question = questionDAO.map(rs, survey);
				survey.addQuestion(question);
			}
			
		} catch (SQLException sqle) {
			throw new DAOException(sqle);
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException sqle) {
					//log error
				}
			}
			
			if (ps != null) {
				try {
					ps.close();
				} catch (SQLException sqle) {
					//log error
				}
			}
		}
		return survey;
	}
	
	public Survey map(ResultSet resultSet) throws SQLException{
		Survey survey = new Survey();
		survey.setId(resultSet.getInt("survey.id"));
		survey.setGroupID(resultSet.getInt("survey.groupID"));
		survey.setCreationdate(resultSet.getDate("survey.creationdate"));
		survey.setStartdate(resultSet.getDate("survey.startdate"));
		survey.setEnddate(resultSet.getDate("survey.enddate"));
		survey.setTitle(resultSet.getString("survey.title"));
		survey.setDescription(resultSet.getString("survey.description"));
		survey.setLanguage(resultSet.getString("survey.language").equals("EN") ? Language.EN : Language.NL);
		return survey;
	}

	@Override
	public SortedSet<Survey> findAllSurveysForUser(User user) throws DAOException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		SortedSet<Survey> surveys = new TreeSet<Survey>();
		try {
			String sql = "SELECT * FROM survey LEFT JOIN usersurvey ON usersurvey.surveyID = survey.id AND usersurvey.userID = ? WHERE usersurvey.id IS null AND survey.language = ?";
			ps = connection.prepareStatement( sql );
			ps.setInt(1, user.getId());
			ps.setString(2, user.getLanguage().equals(Language.EN) ? "EN" : "NL");
			rs = ps.executeQuery();
			while (rs.next()) {
				Survey survey = map(rs);
				surveys.add(survey);
			}
			
		} catch (SQLException sqle) {
			throw new DAOException(sqle);
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException sqle) {
					//log error
				}
			}
			
			if (ps != null) {
				try {
					ps.close();
				} catch (SQLException sqle) {
					//log error
				}
			}
		}
		return surveys;
	}

}
